﻿///***************************************************************
/// 시  스  템 : SMFramework
/// 단위시스템 : Util
/// 계      층 : Framework Utility
/// 프로그램명 : ExcelUtil
/// 파  일  명 : ExcelUtil.cs
/// 설      명 : 엑셀관련 유틸리티
///***************************************************************
/// 수정ID 일자       이름     내용
///---------------------------------------------------------------
/// CH001  2008-07-08 송시명   최초 작성
///***************************************************************
///
using System;
using System.Collections;
using System.Data;
using System.Drawing;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System.Reflection;
using System.Diagnostics;
using System.Collections.Generic;

using SMFramework.Configuration;

namespace SMFramework.Utility
{
	/// <summary>
	/// 엑셀관련 유틸리티
	/// </summary>
	public class ExcelUtil
	{
		/// <summary>
		/// Page
		/// </summary>
		protected static System.Web.UI.Page go_page;

		#region enum
		
		/// <summary>
		/// 수평정렬
		/// </summary>
		public enum HAlign
		{
			Left,
			Center,
			Right,
			Justify
		}
		
		/// <summary>
		/// 수직정렬
		/// </summary>
		public enum VAlign
		{
			Top,
			Middle,
			Bottom,
			NotSet
		}
		
		#endregion

		#region 수평정렬 설정

		/// <summary>
		/// 엑셀다운로드 / 수평정렬 설정
		/// </summary>
		/// <param name="hAlign">정렬구분값(L:Left, C:Center, R:Right)</param>
		/// <returns>HorizontalAlign</returns>
		public static HorizontalAlign GetHorizontalAlign(HAlign hAlign)
		{
			HorizontalAlign ha = new HorizontalAlign();
			switch (hAlign)
			{
				case HAlign.Left:
					ha = HorizontalAlign.Left;
					break;
				case HAlign.Center:
					ha = HorizontalAlign.Center;
					break;
				case HAlign.Right:
					ha = HorizontalAlign.Right;
					break;
				case HAlign.Justify:
					ha = HorizontalAlign.Justify;
					break;
				default:
					ha = HorizontalAlign.Left;
					break;
			}
			return ha;
		} 
		
		#endregion

		#region 수직정렬 설정

		/// <summary>
		/// 엑셀다운로드 / 수직정렬 설정
		/// </summary>
		/// <param name="vAlign">정렬구분값</param>
		/// <returns>HorizontalAlign</returns>
		public static VerticalAlign GetVerticalAlign(VAlign vAlign)
		{
			VerticalAlign va = new VerticalAlign();
			switch (vAlign)
			{
				case VAlign.Top:
					va = VerticalAlign.Top;
					break;
				case VAlign.Middle:
					va = VerticalAlign.Middle;
					break;
				case VAlign.Bottom:
					va = VerticalAlign.Bottom;
					break;
				case VAlign.NotSet:
					va = VerticalAlign.NotSet;
					break;
				default:
					va = VerticalAlign.NotSet;
					break;
			}
			return va;
		}

		#endregion

		#region 반복컬럼 생성(기본값)

		/// <summary>
		/// 반복컬럼 생성(기본값)
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="headerText"></param>
		/// <returns></returns>
		public static BoundColumn CreateBoundColumn(string dataField, string headerText)
		{
			return CreateBoundColumn(dataField, headerText, "", HAlign.Left, VAlign.NotSet);
		}
		
		#endregion

		#region 반복컬럼 생성(기본값, 포맷지정)

		/// <summary>
		/// 반복컬럼 생성(기본값, 포맷지정)
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="headerText"></param>
		/// <param name="formatString"></param>
		/// <returns></returns>
		public static BoundColumn CreateBoundColumn(string dataField, string headerText, string formatString)
		{
			return CreateBoundColumn(dataField, headerText, formatString, HAlign.Left, VAlign.NotSet);
		}

		#endregion

		#region 반복컬럼 생성(기본값, 포맷지정, 수평정렬)

		/// <summary>
		/// 반복컬럼 생성(기본값, 포맷지정, 수평정렬)
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="headerText"></param>
		/// <param name="formatString"></param>
		/// <param name="hAlign"></param>
		/// <returns></returns>
		public static BoundColumn CreateBoundColumn(string dataField, string headerText, string formatString, HAlign hAlign)
		{
			return CreateBoundColumn(dataField, headerText, formatString, hAlign, VAlign.NotSet);
		}
		
		#endregion

		#region 반복컬럼 생성(기본값, 포맷지정, 수평정렬, 수직정렬)

		/// <summary>
		/// 반복컬럼 생성(기본값, 포맷지정, 수평정렬, 수직정렬)
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="headerText"></param>
		/// <param name="formatString"></param>
		/// <param name="hAlign"></param>
		/// <param name="vAlign"></param>
		/// <returns></returns>
		public static BoundColumn CreateBoundColumn(string dataField, string headerText, string formatString, HAlign hAlign, VAlign vAlign)
		{
			BoundColumn column = new BoundColumn();

			column.DataField = dataField;
			column.HeaderText = headerText;
			column.DataFormatString = formatString;
			column.ItemStyle.HorizontalAlign = GetHorizontalAlign(hAlign);
			column.ItemStyle.VerticalAlign = GetVerticalAlign(vAlign);
			column.ItemStyle.Font.Name = WebConfig.SMFramework["Excel.Font"];
			column.ItemStyle.Font.Size = FontUnit.Point(Convert.ToInt32(WebConfig.SMFramework["Excel.FontSize"]));

			return column;
		}

		#endregion

		#region 엑셀다운로드
		/// <summary>
		/// 엑셀다운로드
		/// </summary>
		/// <param name="pPage"></param>
		/// <param name="pFileName"></param>
		/// <param name="pDataSet"></param>
		/// <param name="pColumns"></param>
		public static void CreateExcelFile(System.Web.UI.Page pPage, string pFileName, DataSet pDataSet, ArrayList pColumns)
		{
			CreateExcelFile(pPage, pFileName, pDataSet, pColumns, null);
		}

		/// <summary>
		/// 엑셀다운로드
		/// </summary>
		/// <param name="pPage"></param>
		/// <param name="pFileName"></param>
		/// <param name="pDataSet"></param>
		/// <param name="pColumns"></param>
		/// <param name="headerTexts"></param>
		public static void CreateExcelFile(System.Web.UI.Page pPage, string pFileName, DataSet pDataSet, ArrayList pColumns, string[] headerTexts)
		{
			go_page = pPage;
			DataGrid lo_dg = new DataGrid();

			lo_dg.ShowHeader = true;
			lo_dg.AutoGenerateColumns = false;

			int i = 0;

			// Bound Column
			foreach (object column in pColumns)
			{				
				BoundColumn bc = (BoundColumn)column;
				
				//헤더 다국어적용
				if (headerTexts != null && headerTexts.Length > 0)
				{
					bc.HeaderText = headerTexts[i];
				}
				lo_dg.Columns.Add(bc);
				i++;
			}

			// DataGrid Style
			lo_dg.HeaderStyle.BackColor		= System.Drawing.Color.FromName(WebConfig.SMFramework["Excel.HeaderBackColor"]);
			lo_dg.HeaderStyle.ForeColor = System.Drawing.Color.FromName(WebConfig.SMFramework["Excel.HeaderForeColor"]);
			lo_dg.HeaderStyle.HorizontalAlign	= HorizontalAlign.Center;
			lo_dg.HeaderStyle.Font.Size		= FontUnit.Point(Convert.ToInt32(WebConfig.SMFramework["Excel.HeaderFontSize"]));
			lo_dg.HeaderStyle.Height		= Convert.ToInt32(WebConfig.SMFramework["Excel.HeaderHeight"]);
			lo_dg.HeaderStyle.Font.Bold		= Convert.ToBoolean(WebConfig.SMFramework["Excel.HeaderBold"]);

			//한글 파일명 처리
			string lsFileName = HttpUtility.UrlEncode(pFileName, new UTF8Encoding());

			lo_dg.DataSource = pDataSet;
			lo_dg.DataBind();

			go_page.Response.AddHeader("Content-Disposition", string.Format("attachment;fileName={0}", lsFileName));
			go_page.Response.ContentType = "application/vnd.ms-excel";//"application/unknown";

			lo_dg.EnableViewState = false;

			StringWriter tw = new StringWriter();
			HtmlTextWriter hw = new HtmlTextWriter(tw);

			lo_dg.RenderControl(hw);

			go_page.Response.Write("<META HTTP-EQUIV=\"Content-Type\" CONTENT=\"text/html;\" charset=\"" + WebConfig.SMFramework["Excel.CharacterSet"] + "\">");

			String convStr = tw.ToString();
			go_page.Response.Write(convStr);

			go_page.Response.End();
			lo_dg.Dispose();
		}

		#endregion

		#region 반복컬럼 생성(기본값)

		/// <summary>
		/// 반복컬럼 생성(기본값)
		/// </summary>
		/// <param name="dataField"></param>
		/// <returns></returns>
		public static BoundColumn CreateColumn(string dataField)
		{
			return CreateColumn(dataField, "", HAlign.Left, VAlign.NotSet);
		}

		#endregion

		#region 반복컬럼 생성(기본값, 포맷지정)

		/// <summary>
		/// 반복컬럼 생성(기본값, 포맷지정)
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="formatString"></param>
		/// <returns></returns>
		public static BoundColumn CreateColumn(string dataField, string formatString)
		{
			return CreateColumn(dataField, formatString, HAlign.Left, VAlign.NotSet);
		}

		#endregion

		#region 반복컬럼 생성(기본값, 포맷지정, 수평정렬)

		/// <summary>
		/// 반복컬럼 생성(기본값, 포맷지정, 수평정렬)
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="formatString"></param>
		/// <param name="hAlign"></param>
		/// <returns></returns>
		public static BoundColumn CreateColumn(string dataField, string formatString, HAlign hAlign)
		{
			return CreateColumn(dataField, formatString, hAlign, VAlign.NotSet);
		}

		#endregion

		#region 반복컬럼 생성(기본값, 수평정렬) - 수직기본값 : NotSet

		/// <summary>
		/// 반복컬럼 생성(기본값, 수평정렬) - 수직기본값 : NotSet
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="hAlign"></param>
		/// <returns></returns>
		public static BoundColumn CreateColumn(string dataField, HAlign hAlign)
		{
			return CreateColumn(dataField, "", hAlign, VAlign.NotSet);
		}

		#endregion

		#region 반복컬럼 생성(기본값, 수직정렬) - 수평기본값 : Left

		/// <summary>
		/// 반복컬럼 생성(기본값, 수직정렬) - 수평기본값 : Left
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="vAlign"></param>
		/// <returns></returns>
		public static BoundColumn CreateColumn(string dataField, VAlign vAlign)
		{
			return CreateColumn(dataField, "", HAlign.Left, vAlign);
		}

		#endregion

		#region 반복컬럼 생성(기본값, 수평정렬, 수직정렬)

		/// <summary>
		/// 반복컬럼 생성(기본값, 수평정렬, 수직정렬)
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="hAlign"></param>
		/// <param name="vAlign"></param>
		/// <returns></returns>
		public static BoundColumn CreateColumn(string dataField, HAlign hAlign, VAlign vAlign)
		{
			return CreateColumn(dataField, "", hAlign, vAlign);
		}

		#endregion

		#region 반복컬럼 생성(기본값, 포맷지정, 수평정렬, 수직정렬)

		/// <summary>
		/// 반복컬럼 생성(기본값, 포맷지정, 수평정렬, 수직정렬)
		/// </summary>
		/// <param name="dataField"></param>
		/// <param name="formatString"></param>
		/// <param name="hAlign"></param>
		/// <param name="vAlign"></param>
		/// <returns></returns>
		public static BoundColumn CreateColumn(string dataField, string formatString, HAlign hAlign, VAlign vAlign)
		{
			BoundColumn column = new BoundColumn();

			column.DataField = dataField;
			column.DataFormatString = formatString;
			column.ItemStyle.HorizontalAlign = GetHorizontalAlign(hAlign);
			column.ItemStyle.VerticalAlign = GetVerticalAlign(vAlign);
			column.ItemStyle.Font.Name = WebConfig.SMFramework["Excel.Font"];
			column.ItemStyle.Font.Size = FontUnit.Point(Convert.ToInt32(WebConfig.SMFramework["Excel.FontSize"]));

			return column;
		}

		#endregion

		#region 엑셀다운로드(XLSX)

		#region CreateExcelFileX
		/// <summary>
		/// 엑셀다운로드(XLSX) - 데이터셋의 모든 데이터테이블을 엑셀의 sheet로 생성
		/// </summary>
		/// <param name="pPage"></param>
		/// <param name="pFileName"></param>
		/// <param name="pDataSet"></param>
		public static void CreateExcelFileX(System.Web.UI.Page pPage, string pFileName, DataSet pDataSet)
		{
			go_page = pPage;
			if (Convert.ToBoolean(WebConfig.SMFramework["Excel.TimeStamp"]))
			{
				pFileName = pFileName + "_" + DateUtil.GetNow(WebConfig.SMFramework["Excel.TimeStamp.Format"]);
			}
			string fileName = pFileName.Contains(".xlsx") ? pFileName : pFileName + ".xlsx";

			if (ExcelUtil.CreateExcelDocument(pDataSet, fileName))
			{
				//한글 파일명 처리
				string lsFileName = HttpUtility.UrlEncode(fileName, new UTF8Encoding());

				go_page.Response.ClearContent();
				FileStream fs1 = new FileStream(fileName, FileMode.Open, FileAccess.Read);
				byte[] data1 = new byte[fs1.Length];
				fs1.Read(data1, 0, data1.Length);

				go_page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
				go_page.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
				go_page.Response.BinaryWrite(data1);
				go_page.Response.End();
			}
		}

		/// <summary>
		/// 엑셀다운로드(XLSX) - 넘어온 헤더정보로 엑셀의 첫행처리. 첫번째 데이터테이블만 처리
		/// </summary>
		/// <param name="pPage"></param>
		/// <param name="pFileName"></param>
		/// <param name="pDataSet"></param>
		/// <param name="headerTexts"></param>
		public static void CreateExcelFileX(System.Web.UI.Page pPage, string pFileName, DataSet pDataSet, string[] headerTexts)
		{
			CreateExcelFileX(pPage, pFileName, pDataSet, 0, headerTexts);
		}

		/// <summary>
		/// 엑셀다운로드(XLSX) - 넘어온 헤더정보로 엑셀의 첫행처리. 특정 데이터테이블 처리
		/// </summary>
		/// <param name="pPage"></param>
		/// <param name="pFileName"></param>
		/// <param name="pDataSet"></param>
		/// <param name="dtIdx"></param>
		/// <param name="headerTexts"></param>
		public static void CreateExcelFileX(System.Web.UI.Page pPage, string pFileName, DataSet pDataSet, int dtIdx, string[] headerTexts)
		{
			go_page = pPage;
			if (Convert.ToBoolean(WebConfig.SMFramework["Excel.TimeStamp"]))
			{
				pFileName = pFileName + "_" + DateUtil.GetNow(WebConfig.SMFramework["Excel.TimeStamp.Format"]);
			}
			string fileName = pFileName.Contains(".xlsx") ? pFileName : pFileName + ".xlsx";

			DataTable dt = new DataTable();

			if (pDataSet.Tables.Count > 0)
			{
				dt = ConvertExcelHeader(pDataSet.Tables[dtIdx], headerTexts);
			}
			pDataSet.Merge(dt);

			if (ExcelUtil.CreateExcelDocument(pDataSet, fileName))
			{
				//한글 파일명 처리
				string lsFileName = HttpUtility.UrlEncode(fileName, new UTF8Encoding());

				go_page.Response.ClearContent();
				FileStream fs1 = new FileStream(fileName, FileMode.Open, FileAccess.Read);
				byte[] data1 = new byte[fs1.Length];
				fs1.Read(data1, 0, data1.Length);

				go_page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
				go_page.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
				go_page.Response.BinaryWrite(data1);
				go_page.Response.End();
			}
		}
		
		#endregion

		#region CreateExcelDocument
		/// <summary>
		/// 엑셀문서 생성
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="list"></param>
		/// <param name="xlsxFilePath"></param>
		/// <returns></returns>
		public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
		{
			DataSet ds = new DataSet();
			ds.Tables.Add(ListToDataTable(list));

			return CreateExcelDocument(ds, xlsxFilePath);
		}

		/// <summary>
		/// 엑셀문서 생성
		/// </summary>
		/// <param name="dt"></param>
		/// <param name="xlsxFilePath"></param>
		/// <returns></returns>
		public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath)
		{
			DataSet ds = new DataSet();
			ds.Tables.Add(dt);

			return CreateExcelDocument(ds, xlsxFilePath);
		}

		/// <summary>
		/// 엑셀문서 생성
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="excelFilename"></param>
		/// <returns></returns>
		public static bool CreateExcelDocument(DataSet ds, string excelFilename)
		{
			try
			{
				using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))
				{
					document.AddWorkbookPart();
					document.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
					document.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

					WorkbookStylesPart workbookStylesPart = document.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
					Stylesheet stylesheet = new Stylesheet();
					workbookStylesPart.Stylesheet = stylesheet;

					CreateParts(ds, document);
				}
				Trace.WriteLine("Successfully created: " + excelFilename);
				return true;
			}
			catch (Exception ex)
			{
				Trace.WriteLine("Failed, exception thrown: " + ex.Message);
				return false;
			}
		}

		#endregion

		#region ListToDataTable

		/// <summary>
		/// 리스트를 데이터테이블로 변환
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="list"></param>
		/// <returns></returns>
		public static DataTable ListToDataTable<T>(List<T> list)
		{
			DataTable dt = new DataTable();

			foreach (PropertyInfo info in typeof(T).GetProperties())
			{
				dt.Columns.Add(new DataColumn(info.Name, GetNullableType(info.PropertyType)));
			}
			foreach (T t in list)
			{
				DataRow row = dt.NewRow();
				foreach (PropertyInfo info in typeof(T).GetProperties())
				{
					if (!IsNullableType(info.PropertyType))
						row[info.Name] = info.GetValue(t, null);
					else
						row[info.Name] = (info.GetValue(t, null) ?? DBNull.Value);
				}
				dt.Rows.Add(row);
			}
			return dt;
		}
		#endregion

		#region private - GetNullableType, IsNullableType, CreateParts, WriteDataTableToExcelWorksheet
		/// <summary>
		/// GetNullableType
		/// </summary>
		/// <param name="t"></param>
		/// <returns></returns>
		private static Type GetNullableType(Type t)
		{
			Type returnType = t;
			if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
			{
				returnType = Nullable.GetUnderlyingType(t);
			}
			return returnType;
		}
		/// <summary>
		/// IsNullableType
		/// </summary>
		/// <param name="type"></param>
		/// <returns></returns>
		private static bool IsNullableType(Type type)
		{
			return (type == typeof(string) ||
					type.IsArray ||
					(type.IsGenericType &&
					 type.GetGenericTypeDefinition().Equals(typeof(Nullable<>))));
		}


		/// <summary>
		/// 데이터셋에 있는 데이터테이블의 수만큼 루프를 돌면서 Sheet를 생성한다.
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="spreadsheet"></param>
		private static void CreateParts(DataSet ds, SpreadsheetDocument spreadsheet)
		{
			uint worksheetNumber = 1;
			foreach (DataTable dt in ds.Tables)
			{
				//For each worksheet you want to create
				string workSheetID = "rId" + worksheetNumber.ToString();
				string worksheetName = dt.TableName;

				WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
				newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

				//create sheet data
				newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

				//save worksheet
				WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
				newWorksheetPart.Worksheet.Save();

				//create the worksheet to workbook relation
				if (worksheetNumber == 1)
					spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

				spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
				{
					Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
					SheetId = (uint)worksheetNumber,
					Name = dt.TableName
				});

				worksheetNumber++;
			}

			spreadsheet.WorkbookPart.Workbook.Save();
		}

		/// <summary>
		/// WriteDataTableToExcelWorksheet
		/// </summary>
		/// <param name="dt"></param>
		/// <param name="worksheetPart"></param>
		private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
		{
			var worksheet = worksheetPart.Worksheet;
			var sheetData = worksheet.GetFirstChild<SheetData>();

			string cellValue = "";

			//  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
			//  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
			//  cells of data, we'll know if to write Text values or Numeric cell values.
			int numberOfColumns = dt.Columns.Count;
			bool[] IsNumericColumn = new bool[numberOfColumns];

			string[] excelColumnNames = new string[numberOfColumns];
			for (int n = 0; n < numberOfColumns; n++)
				excelColumnNames[n] = GetExcelColumnName(n);

			//
			//  Create the Header row in our Excel Worksheet
			//
			uint rowIndex = 1;

			var headerRow = new Row { RowIndex = rowIndex };  // add a row at the top of spreadsheet
			sheetData.Append(headerRow);

			for (int colInx = 0; colInx < numberOfColumns; colInx++)
			{
				DataColumn col = dt.Columns[colInx];
				AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
				IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32");
			}

			//
			//  Now, step through each row of data in our DataTable...
			//
			double cellNumericValue = 0;
			foreach (DataRow dr in dt.Rows)
			{
				// ...create a new row, and append a set of this row's data to it.
				++rowIndex;
				var newExcelRow = new Row { RowIndex = rowIndex };  // add a row at the top of spreadsheet
				sheetData.Append(newExcelRow);

				for (int colInx = 0; colInx < numberOfColumns; colInx++)
				{
					cellValue = dr.ItemArray[colInx].ToString();

					// Create cell with data
					if (IsNumericColumn[colInx])
					{
						//  For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
						//  If this numeric value is NULL, then don't write anything to the Excel file.
						cellNumericValue = 0;
						if (double.TryParse(cellValue, out cellNumericValue))
						{
							cellValue = cellNumericValue.ToString();
							AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
						}
					}
					else
					{
						//  For text cells, just write the input data straight out to the Excel file.
						AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
					}
				}
			}
		}
		#endregion

		#region private - AppendTextCell, AppendNumericCell, GetExcelColumnName
		/// <summary>
		/// AppendTextCell
		/// </summary>
		/// <param name="cellReference"></param>
		/// <param name="cellStringValue"></param>
		/// <param name="excelRow"></param>
		private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)
		{
			//  Add a new Excel Cell to our Row 
			Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
			CellValue cellValue = new CellValue();
			cellValue.Text = cellStringValue;
			cell.Append(cellValue);
			excelRow.Append(cell);
		}
		/// <summary>
		/// AppendNumericCell
		/// </summary>
		/// <param name="cellReference"></param>
		/// <param name="cellStringValue"></param>
		/// <param name="excelRow"></param>
		private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
		{
			//  Add a new Excel Cell to our Row 
			Cell cell = new Cell() { CellReference = cellReference };
			CellValue cellValue = new CellValue();
			cellValue.Text = cellStringValue;
			cell.Append(cellValue);
			excelRow.Append(cell);
		}
		/// <summary>
		/// GetExcelColumnName
		/// </summary>
		/// <param name="columnIndex"></param>
		/// <returns></returns>
		private static string GetExcelColumnName(int columnIndex)
		{
			//  Convert a zero-based column index into an Excel column reference  (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)
			//
			//  eg  GetExcelColumnName(0) should return "A"
			//      GetExcelColumnName(1) should return "B"
			//      GetExcelColumnName(25) should return "Z"
			//      GetExcelColumnName(26) should return "AA"
			//      GetExcelColumnName(27) should return "AB"
			//      ..etc..
			//
			if (columnIndex < 26)
				return ((char)('A' + columnIndex)).ToString();

			char firstChar = (char)('A' + (columnIndex / 26) - 1);
			char secondChar = (char)('A' + (columnIndex % 26));

			return string.Format("{0}{1}", firstChar, secondChar);
		}

		#endregion

		#region private - ConvertExcelHeader

		/// <summary>
		/// DB에서 조회된 데이터테이블의 컬럼수만큼 돌면서 바인딩할 컬럼명이 없으면 삭제
		/// </summary>
		/// <param name="dt"></param>
		/// <param name="headerText"></param>
		/// <returns></returns>
		private static DataTable ConvertExcelHeader(DataTable dt, string[] headerText)
		{
			//headerText에서 컬럼명만 따로 뽑아서 배열에 담아둔다.
			string[] columnNames = new string[headerText.Length];

			if (headerText.Length > 0)
			{
				for (int i = 0; i < headerText.Length; i++)
				{
					if (headerText[i].Contains("^"))
					{
						string[] labels = headerText[i].Split('^');
						columnNames[i] = labels[0];
					}
				}
			}

			//밑에서부터 비교하면서 headerText의 컬럼명이 dt에 없으면 dt의 컬럼을 삭제한다.
			if (dt.Columns.Count > 0)
			{
				int cnt = dt.Columns.Count;
				cnt--;

				for (int i = cnt; i > -1; i--)
				{
					bool isExistColumn = false;
					string db_col_nm = dt.Columns[i].ColumnName;

					for (int j = 0; j < columnNames.Length; j++)
					{
						if (db_col_nm == columnNames[j])
						{
							isExistColumn = true;
						}
					}

					//없으면 삭제
					if (!isExistColumn)
					{
						dt.Columns.Remove(db_col_nm);
					}
				}
			}

			//정리된 dt에서 헤더라벨을 변경한다.
			if (headerText.Length > 0)
			{
				//동일컬럼명 처리용 변수
				int z = 1;
				for (int i = 0; i < headerText.Length; i++)
				{
					if (headerText[i].Contains("^"))
					{
						string[] labels = headerText[i].Split('^');

						string col_nm = labels[0];
						string label_nm = labels[1];

						//일치하는 컬럼만 헤더변경
						if (dt.Columns[col_nm] != null)
						{
							/* 동일한 이름의 column이 있고
							 * 컬럼과 라벨이 다르면 +1씩 붙인다
							 */
							for (int j = 0; j < dt.Columns.Count; j++)
							{
								if (label_nm == dt.Columns[j].ColumnName && col_nm != label_nm)
								{
									label_nm = label_nm + "(" + z++.ToString() + ")";
								}
							}
							
							dt.Columns[col_nm].ColumnName = label_nm;
						}
					}
				}
			}

			return dt;
		}
		#endregion

		#endregion
	}
}
